import requests
from lxml import etree
import csv
import sqlite3
import pymysql

url = 'http://book.zongheng.com/store.html'
response = requests.get(url)
print(response.status_code)
# print(response.text)
html = response.text
# xpath的使用
# 1、得到一个etree对象
ehtml = etree.HTML(html)
# 2、通过xpath查找
# 书名
xtitle1 = ehtml.xpath("//div[@class='bookname']/a/text()")
# print(xtitle1)
# 作者名
xtitle2 = ehtml.xpath("//div[@class='bookilnk']/a[1]/text()")
# print(xtitle2)
# 类型
xtitle3 = ehtml.xpath("//div[@class='bookilnk']/a[2]/text()")
# print(xtitle3)
# 更新时间
xtitle4 = ehtml.xpath("//div[@class='bookilnk']/span[2]/text()")
for i in xtitle4:
    i = i[36:47]
# 图片链接
xtitle5 = ehtml.xpath("//div[@class='bookimg']/a//@src")
# print(xtitle5)
for i in range(len(xtitle4)):
    a = xtitle1[i]
    xtitle1[i] = "".join(a.split())
    a = xtitle2[i]
    xtitle2[i] = "".join(a.split())
    a = xtitle3[i]
    xtitle3[i] = "".join(a.split())
    a = xtitle4[i]
    xtitle4[i] = "".join(a.split())
    a = xtitle5[i]
    xtitle5[i] = "".join(a.split())
def printtxt():
    f = open("1.txt", "w")
    all = str(xtitle1[i].rjust(20,' ')+xtitle2[i].rjust(20,' ')+xtitle3[i].rjust(20,' ')+xtitle4[i].rjust(25,' ')+xtitle5[i].rjust(120,' '))
    print(all)
    f.write(all+"\n")
    # 关闭打开的文件
    f.close()
def printcsv():
    with open("xtitle.csv","a+",newline='') as f:
        w = csv.writer(f)
        for i,j,k,l,o in zip(xtitle1,xtitle2,xtitle3,xtitle4,xtitle5):
            w.writerow([i,j,k,l,o])
            print([i+' '+j+' '+k+' '+l+' '+o+'\n' for i,j,k,l,o in zip(xtitle1,xtitle2,xtitle3,xtitle4,xtitle5)])
def printsql():
    dbname = 'xtitle.db'
    conn = sqlite3.connect(dbname)
    c = conn.cursor()
    sql = '''
            create table if not exists novelTest(
                bookname text not null primary key,
                bookauthor text not null,
                booktype text not null,
                booktime text not null,
                bookimage text not null
            );
            '''
    c.execute(sql)

    insertData = '''
                    INSERT INTO novelTest values(?,?,?,?,?)
                '''
    for i,j,k,l,o in zip(xtitle1,xtitle2,xtitle3,xtitle4,xtitle5):
        c.execute(insertData,(i,j,k,l,o))
    conn.commit()
    conn.close()
    
def printmysql():
    dbConnetion = pymysql.connect(
    host='localhost',
    user='root',
    password='123456',
    database='test',
    charset='utf8'
    );
    #新建游标
    cursor = dbConnetion.cursor()
    datalist = []
    for i,j,k,l,o in zip(xtitle1,xtitle2,xtitle3,xtitle4,xtitle5):
        # 建立一个列表，列表中的元素是元组
        datalist.append((i,j,k,l,o))
    cursor.executemany("INSERT INTO novaltest values(%s,%s,%s,%s,%s)", datalist)
    dbConnetion.commit()
    #关闭数据库连接和游标
    cursor.close()
    dbConnetion.close()
printcsv()
printtxt()
# printsql()
# printmysql()
